//
//  NoteControllerExtensions.swift
//  hello
//
//  Created by 赵康 on 2025/3/31.
//

import Fluent
import Vapor
import FluentSQL
import Redis
import Queues

@Sendable
func search(req: Request) async throws -> [Note.Response] {
  let userID = try req.authenticatedUserID
  
    // 获取查询参数
  guard let query = req.query[String.self, at: "q"], !query.isEmpty else {
    throw Abort(.badRequest, reason: "搜索关键词不能为空")
  }
  
    // 构建缓存键
  let cacheKey = CacheConfig.KeyBuilder.noteSearch(query: query, userID: userID)
  
    // 尝试从缓存获取
  if let cachedResults = try await req.cache.get(
    forKey: cacheKey,
    as: [Note.Response].self,
    from: req.redis
  ) {
    return cachedResults
  }
  
    // 查询数据库
    // 使用 SQL LIKE 操作符进行简单的全文搜索
  guard let sql = req.db as? SQLDatabase else {
    throw Abort(.internalServerError, reason: "数据库连接错误")
  }
  
  let sqlQuery = SQLQueryString("""
SELECT n.id, n.content, n.timestamp, n.tags, n.user, n.audio, n.isPublic, n.createdAt, n.updatedAt,
       n.likeCount, n.likedBy, n.answer, n.isArabic, n.isAnswerArabic,
       a.title as audioTitle, a.cover as audioCover, 
       al.cover as albumCover,
       u.name as userName, u.avatar as userAvatar
FROM \(unsafeRaw: Note.schema) n
JOIN \(unsafeRaw: Audio.schema) a ON n.audio = a.id
JOIN \(unsafeRaw: Album.schema) al ON a.album = al.id
JOIN \(unsafeRaw: User.schema) u ON n.user = u.id
WHERE n.user = \(bind: userID)
AND a.id != \(bind: UUID(uuidString: "00000000-0000-0000-0000-000000000000")!)
AND (n.content LIKE \(bind: "%\(query)%") 
     OR JSON_CONTAINS(n.tags, \(bind: "\"\(query)\""))
)
ORDER BY n.updatedAt DESC
""")
  
  let rows = try await sql.raw(sqlQuery).all()
  let responses = try rows.map { row in
      // 解析 likedBy 数组
    let likedByData = try row.decode(column: "likedBy", as: Data?.self) ?? Data("[]".utf8)
    let likedBy = try JSONDecoder().decode([UUID].self, from: likedByData)
    
    return Note.Response(
      id: try row.decode(column: "id", as: UUID.self),
      content: try row.decode(column: "content", as: String.self),
      timestamp: try row.decode(column: "timestamp", as: Double?.self) ?? 0,
      tags: try row.decode(column: "tags", as: [String]?.self) ?? [],
      userID: try row.decode(column: "user", as: UUID.self),
      audioID: try row.decode(column: "audio", as: UUID.self),
      isPublic: try row.decode(column: "isPublic", as: Bool.self),
      
      // 点赞信息
      likeCount: try row.decode(column: "likeCount", as: Int.self),
      isLiked: likedBy.contains(userID),
      
      // 新增关联数据
      audioTitle: try row.decode(column: "audioTitle", as: String.self),
      audioCover: try row.decode(column: "audioCover", as: String?.self),
      albumCover: try row.decode(column: "albumCover", as: String?.self),
      userName: try row.decode(column: "userName", as: String.self),
      userAvatar: try row.decode(column: "userAvatar", as: String?.self),
      reviewStatus: try {
        let statusString = try row.decode(column: "reviewStatus", as: String.self)
        return ReviewStatus(rawValue: statusString) ?? ReviewStatus.none
      }(),
      answer: try row.decode(column: "answer", as: String?.self),
      isArabic: try row.decode(column: "isArabic", as: Bool.self),
      isAnswerArabic: try row.decode(column: "isAnswerArabic", as: Bool.self),
      createdAt: try row.decode(column: "createdAt", as: Date?.self),
      
      updatedAt: try row.decode(column: "updatedAt", as: Date?.self)
    )
  }
  
    // 缓存结果
  try await req.cache.set(
    responses,
    forKey: cacheKey,
    duration: CacheConfig.Duration.short, // 搜索结果缓存时间较短
    type: .note,
    on: req.redis
  )
  
  return responses
}
